package fr.inserm.u674.chcdbweb.model.DAO;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import fr.inserm.u674.chcdbweb.model.DTO.User;

/**
 * This class represents a SQL Database Access Object for the {@link User} DTO. This DAO should be
 * used as a central point for the mapping between the User DTO and a SQL database.
 * 
 * @author BalusC, Thomas Burguiere
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public final class UserDAO {

	// CONSTANTS

	private static final String SQL_FIND_BY_INITIALS = "SELECT * FROM User WHERE initials = ?";
	private static final String SQL_FIND_FIRST_AND_LAST_NAME =
		"SELECT * FROM User WHERE firsName = ? AND LASTNAME = ?";
	private static final String SQL_LIST_ORDER_BY_LAST_NAME =
		"SELECT * FROM User ORDER BY lastName";

	private static final String SQL_INSERT =
		"INSERT INTO User (initials, firstName, lastName) VALUES (?,?,?)";
	private static final String SQL_UPDATE =
		"UPDATE User SET initials=?, firstName=?, lastName=? WHERE initials = ? ";
	private static final String SQL_DELETE = "DELETE FROM User WHERE initials = ?";

	// VARS

	private DAOFactory daoFactory;

	// CONSTRUCTORS

	public UserDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// ACTIONS

	/**
	 * @param initials
	 * @return
	 * @throws DAOException
	 */
	public User find(String initials) throws DAOException {
		return find(SQL_FIND_BY_INITIALS, initials);
	}

	/**
	 * @param firstName
	 * @param lastName
	 * @return
	 * @throws DAOException
	 */
	public User find(String firstName, String lastName) throws DAOException {
		return find(SQL_FIND_FIRST_AND_LAST_NAME, firstName, lastName);
	}

	/**
	 * @param sql
	 * @param values
	 * @return
	 * @throws DAOException
	 */
	private User find(String sql, Object... values) throws DAOException {
		User user = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			user = mapUser(resultSet);
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

		return user;
	}

	/**
	 * Returns a list of all users from the database ordered by user last name. The list is never
	 * null and is empty when the database does not contain any user.
	 * 
	 * @return A list of all users from the database ordered by user last name.
	 * @throws DAOException
	 *             If something fails at database level.
	 */
	public List<User> list() throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<User> users = new ArrayList<User>();
		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(SQL_LIST_ORDER_BY_LAST_NAME);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				users.add(mapUser(resultSet));
			}
			return users;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * @param user
	 * @throws DAOException
	 */
	public void create(User user) throws DAOException {
		Object[] values = { user.getInitials(), user.getFirstName(), user.getLastName() };

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_INSERT, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("User creation failed, no rows affected in the database.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	/**
	 * @param user
	 * @throws DAOException
	 */
	public void update(User user) throws DAOException {
		if (user.getInitials() == null) {
			throw new IllegalArgumentException("User is not created yet, the initials are null.");
		}

		Object[] values = { user.getInitials(), user.getFirstName(), user.getLastName() };
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_UPDATE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("User update failed, no rows affected in the database.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement);
		}
	}

	/**
	 * @param user
	 * @throws DAOException
	 */
	public void save(User user) throws DAOException {
		if (user.getInitials() == null)
			create(user);
		else
			update(user);
	}

	/**
	 * @param user
	 * @throws DAOException
	 */
	public void delete(User user) throws DAOException{
		Object[] values = {user.getInitials()};
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try{
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, SQL_DELETE, false, values);
			int affectedRows = preparedStatement.executeUpdate();
			if(affectedRows == 0)
				throw new DAOException("User delete failed, no rows affected in the database.");
		}
		catch(SQLException e){
			throw new DAOException(e);
		}
		finally{
			close(connection, preparedStatement);
		}
	}
	// HELPERS

	private static User mapUser(ResultSet resultSet) throws SQLException {
		return new User(resultSet.getString("initials"), resultSet.getString("firstName"),
			resultSet.getString("lastName"));
	}

}
